﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using PMPUtils.DB;
using PMPUtils.Resources;
using System.Collections;

namespace PMPUtils.Checklist
{
    public class ChecklistOperator
    {
        /// <summary>
        /// Add components to the available tree views
        /// </summary>
        /// <param name="trvAvailable"></param>
        public static void RefreshAvaliableTreeView(TreeView trvAvailable)
        {
            // Clean the nodes
            trvAvailable.Nodes.Clear();

            // Readd the nodes
            TreeNode root = new TreeNode("Available");
            trvAvailable.Nodes.Add(root);

            DataTable dtAvailables = ReadAvailableChecklists();
            foreach (DataRow row in dtAvailables.Rows)
            {
                if (row["component"] != DBNull.Value)
                {
                    String component = row["component"].ToString();
                    TreeNode componentNode = new TreeNode(component, component);

                    root.ChildNodes.Add(componentNode);
                }
            }
        }

        /// <summary>
        /// Read all the available components
        /// </summary>
        /// <returns></returns>
        public static DataTable ReadAvailableChecklists()
        {
            String strQuery = "select distinct component from checklist order by component";
            DBOperator dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            DataTable dtAvailableChecklist = dbOperator.Read(strQuery);

            return dtAvailableChecklist;
        }

        /// <summary>
        /// Refresh the assinged checklist
        /// </summary>
        /// <param name="trvAssigned"></param>
        /// <param name="taskID"></param>
        public static void RefreshAssignedChecklist(TreeView trvAssigned, String taskID)
        {
            // Clear the noides
            trvAssigned.Nodes.Clear();

            // Readd the nodes
            TreeNode root = new TreeNode("Assigned");
            trvAssigned.Nodes.Add(root);

            DataTable dtAssigned = ReadAssignedChecklists(taskID);
            foreach (DataRow row in dtAssigned.Rows)
            {
                if (row["component"] != DBNull.Value)
                {
                    String component = row["component"].ToString();
                    TreeNode componentNode = new TreeNode(component, component);

                    root.ChildNodes.Add(componentNode);
                }
            }
        }

        /// <summary>
        /// Refresh the gridview of the assigned
        /// </summary>
        /// <param name="grvAssigned"></param>
        /// <param name="taskID"></param>
        public static void RefreshAssignedGridView(GridView grvAssigned, String taskID)
        {
            DataTable dtAssigned = ReadAssignedChecklists(taskID);

            grvAssigned.DataSource = dtAssigned.DefaultView;
            grvAssigned.DataBind();
        }

        public static void RefreshReport(GridView grvReport, String taskID)
        {
            DataTable dtResults = ReadChecklistReport(taskID);

            grvReport.DataSource = dtResults.DefaultView;
            grvReport.DataBind();
        }

        public static DataTable ReadChecklistReport(String taskID)
        {
            String strQuery = " select item_desc, results " +
                " from checklist_task " +
                " join checklist on (checklist_task.checklist_id = checklist.ID) " +
                " where rev = (select max(rev) from checklist_task)";

            DBOperator dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            DataTable dtResults = dbOperator.Read(strQuery);

            return dtResults;
        }

        /// <summary>
        /// Read the checklist assigned to the task
        /// </summary>
        /// <param name="taskID"></param>
        /// <returns></returns>
        public static DataTable ReadAssignedChecklists(String taskID)
        {
            String strQuery = "select distinct component from checklist_task where task_id = '" + taskID + "' order by component";
            DBOperator dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            DataTable dtAssignedChecklist = dbOperator.Read(strQuery);

            return dtAssignedChecklist;
        }

        /// <summary>
        /// Add a new checklist for the task.
        /// </summary>
        /// <param name="taskID"></param>
        /// <param name="component"></param>
        public static void AssignChecklist(String taskID, String component)
        {
            DataTable dtChecklistItems = ReadChecklistItems(component);
            foreach (DataRow row in dtChecklistItems.Rows)
            {
                if (row["item"] != DBNull.Value)
                {
                    String id = row["id"].ToString();
                    String item = row["item"].ToString();

                    String strInsert = "insert into checklist_task (task_id, checklist_id, component, item, rev) values " +
                        " ('" + taskID + "', " + id + ", '" + component + "', '" + item + "', 0)";
                    DBOperator dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
                    dbOperator.Update(strInsert);
                }
            }
        }

        /// <summary>
        /// Read the checklist item for the component
        /// </summary>
        /// <param name="component"></param>
        /// <returns></returns>
        public static DataTable ReadChecklistItems(String component)
        {
            String strQuery = "select id, item from checklist where component = '" + component + "' order by item";
            DBOperator dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            DataTable dtChecklistItem = dbOperator.Read(strQuery);

            return dtChecklistItem;
        }

        /// <summary>
        /// drop the specific assigned checklist
        /// </summary>
        /// <param name="ID"></param>
        public static void DropAssignedChecklist(String component)
        {
            String strDrop = "delete from checklist_task where component = '" + component + "'";
            DBOperator dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            dbOperator.Update(strDrop);
        }

        /// <summary>
        /// Check based on assigned checklist
        /// </summary>
        public static void Check(String taskID)
        {
            Hashtable executables = ReadExecutible(taskID);

            foreach (String key in executables.Keys)
            {
                String exe = executables[key].ToString();

                if (key.StartsWith("SQL"))
                {
                    CheckBySQL(taskID, exe);
                }
                else
                {
                    CheckByEXE(exe);
                }
            }
        }

        public static void CheckBySQL(String taskID, String sqlStatement)
        {
            DBOperator dbOperator = new DBOperator(DBConfigurations.JIRA_SQL_DB_CONNECTION);
            DataTable dtResults = dbOperator.Read(sqlStatement);

            int currentMaxRev = ReadMaxRev(taskID);
            int maxRev = currentMaxRev + 1;

            foreach (DataRow row in dtResults.Rows)
            {
                if (row["item"] != DBNull.Value)
                {
                    String JIRA = row["JIRA"].ToString();
                    String item = row["item"].ToString();
                    String result = row["Result"].ToString();

                    String strUpdate2 = "update checklist_task set rev = " + maxRev +
                        " where task_id = '" + JIRA + "'" +
                        "   and rev = " + currentMaxRev;
                    DBOperator updateOperator2 = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
                    updateOperator2.Update(strUpdate2);

                    String strUpdate = "update checklist_task set " +
                        " results = '" + result.Replace("\'", "\"") + "'" +
                        " where task_id = '" + JIRA + "'" +
                        "   and item = '" + item + "'" + 
                        "   and rev = " + maxRev;
                    DBOperator updateOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
                    updateOperator.Update(strUpdate);
                }
            }
        }

        private static int ReadMaxRev(String taskID)
        {
            String strQuery = "select max(rev) rev from checklist_task where task_id = '" + taskID + "'";
            DBOperator dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            DataTable dtMaxRev = dbOperator.Read(strQuery);

            if (dtMaxRev.Rows.Count <= 0)
            {
                return 0;
            }
            else
            {
                foreach (DataRow row in dtMaxRev.Rows)
                {
                    if (row["rev"] != DBNull.Value)
                    {
                        return Convert.ToInt32(row["rev"]);
                    }
                }
            }

            return 0;
        }

        public static void CheckByEXE(String exe)
        {
        }

        private static Hashtable ReadExecutible(String taskID)
        {
            Hashtable executables = new Hashtable();

            String strQuery = " select distinct checklist.exe_type, checklist_exe.exe " +
                " from checklist_task " +
                " join checklist " +
                " on (checklist.id = checklist_task.checklist_id) " +
                " join checklist_exe " +
                " on (checklist_exe.exe_id = checklist.exe_id)";
            DBOperator dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            DataTable dtExecutables = dbOperator.Read(strQuery);

            int i = 0;
            foreach (DataRow row in dtExecutables.Rows)
            {
                if (row["exe_type"] != DBNull.Value)
                {
                    String exeType = row["exe_type"].ToString();

                    if (exeType.Equals("SQL"))
                    {
                        String sqlStatement = row["exe"].ToString().Replace("\"", "\'");
                        sqlStatement = sqlStatement.Replace("CHECKLISTJIRA", "'" + taskID + "'");

                        executables.Add("SQL" + i, sqlStatement);
                    }
                    else
                    {
                        String exe = row["exe"].ToString();
                        executables.Add("EXE" + i, exe);
                    }
                }
                
                i++;
            }

            return executables;
        }
    }
}